﻿using Lamex.Base;
using Lamex.Interface;
using Lamex.Library;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Lamex.mDashboard.UI
{
    public partial class FrmTableBoardDull : frmBaseChild
    {
        public FrmTableBoardDull()
        {
            InitializeComponent();
            DateEditFormat();
        }

        private void FrmTableBoardDull_Load(object sender, EventArgs e)
        {
            try { this.InitButtons(); } catch { }//初始化标准功能，因要应用于RibbonForm，所以加入异常处理
        }
        public void DateEditFormat()
        {
            this.dtStartTime.Properties.DisplayFormat.FormatString = "yyyy-MM-dd";
            this.dtStartTime.Properties.DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.dtStartTime.Properties.EditFormat.FormatString = "yyyy-MM-dd";
            this.dtStartTime.Properties.EditFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.dtStartTime.Properties.Mask.EditMask = "yyyy-MM-dd";

            this.dtEndTime.Properties.DisplayFormat.FormatString = "yyyy-MM-dd";
            this.dtEndTime.Properties.DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.dtEndTime.Properties.EditFormat.FormatString = "yyyy-MM-dd";
            this.dtEndTime.Properties.EditFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.dtEndTime.Properties.Mask.EditMask = "yyyy-MM-dd";

            DateTime dt = DateTime.Now;
            dtStartTime.Text = dt.AddMonths(-dt.Month + 1).AddDays(-dt.Day + 1).ToString("yyyy-MM-dd"); //本年第一天
            dtEndTime.Text = dt.ToString("yyyy-MM-dd");//当前日期
        }
        /// 查询
        /// </summary>
        /// <param name="sender"></param>
        public override void DoView(Lamex.Interface.IButtonInfo sender)
        {
            //一定要选择时间
            if (string.IsNullOrEmpty(dtStartTime.Text))
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("请选择开始时间", "提示", MessageBoxButtons.OK);
                return;
            }
            if (string.IsNullOrEmpty(dtEndTime.Text))
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("请选择结束时间", "提示", MessageBoxButtons.OK);
                return;
            }
            if (string.IsNullOrEmpty(cbWeek.Text))
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("请选择汇总类型", "提示", MessageBoxButtons.OK);
                return;
            }
           
            gridView1.Columns.Clear();
            DataTable dt = GetData(cbWeek.Text);
            gridControl1.DataSource = dt;
            gridView1.BestFitColumns();
            this.gridView1.OptionsView.ColumnAutoWidth = false;
            base.DoView(sender);
        }

        public DataTable GetData(string Weeks)
        {
            int i2 = 0, i2_1 = 0, i4 = 0, i4_1 = 0, i8 = 0, i8_1 = 0, i12 = 0, i12_1 = 0;
            //只统计出库的记录
            StringBuilder sbFilter = new StringBuilder();
            List<string> list = new List<string>();
            string YMW = "  CONVERT(datetime,CONVERT(VARCHAR(10),Jahr)+'-'+ CONVERT(VARCHAR(10),Monat)+'-'+ CONVERT(VARCHAR(10),Tag))";
            string MaterialNo = @" (select distinct MATERIAL_NO FROM [StorageManager].[dbo].[T3_LAYER])";
            string sql = @"select MaterialID as '物料编号',datepart(WEEK, CONVERT(VARCHAR(10),Jahr)+'-'+ CONVERT(VARCHAR(10),Monat)+'-'+ CONVERT(VARCHAR(10),Tag)) as 'week', sum(Anzahl) as 'QTY' from [StorageManager].[dbo].[T3_Material_BDE] 
	WHERE MaterialID in " + MaterialNo + " and Ereignistyp='2' and " + YMW + ">='" + dtStartTime.Text + "' and " + YMW + "<='" + dtEndTime.Text + "' group by  MaterialID,datepart(WEEK, CONVERT(VARCHAR(10),Jahr)+'-'+ CONVERT(VARCHAR(10),Monat)+'-'+ CONVERT(VARCHAR(10),Tag))";


            DataTable dt = BaseBuildSql.GetDataTableBySql(sql);
            DataTable dtNew = createTable(dt, "Week", "物料编号", "QTY");
            for (int i = 0; i < dtNew.Rows.Count; i++)
            {
                for (int j = dtNew.Columns.Count - 1; j > 0; j--)
                {
                    #region 按周统计
                    switch (Weeks)
                    {
                        case "2周":
                            if (i2 < 2)
                            {
                                if (dtNew.Rows[i][j].ToString().Length <= 0)
                                {
                                    i2_1 = i2_1 + 1;
                                }
                                if (i2_1 == 2)
                                {
                                    list.Add(dtNew.Rows[i]["物料编号"].ToString());
                                }
                                i2 = i2 + 1;
                            }
                            //if (dtNew.Rows[i][j].ToString().Length <= 0)
                            //{
                            //    i2 = i2 + 1;
                            //    if (i2 == 2)
                            //    {
                            //        list.Add(dtNew.Rows[i]["物料编号"].ToString());
                            //        continue;
                            //    }
                            //}
                            //else
                            //{
                            //    i2 = 0;
                            //}
                            break;
                        case "4周":
                            if (i4 < 4)
                            {
                                if (dtNew.Rows[i][j].ToString().Length <= 0)
                                {
                                    i4_1 = i4_1 + 1;
                                }
                                if (i4_1 == 4)
                                {
                                    list.Add(dtNew.Rows[i]["物料编号"].ToString());
                                }
                                i4 = i4 + 1;
                            }
                            break;
                        case "8周":
                            if (i8 < 8)
                            {
                                if (dtNew.Rows[i][j].ToString().Length <= 0)
                                {
                                    i8_1 = i8_1 + 1;
                                }
                                if (i8_1 == 8)
                                {
                                    list.Add(dtNew.Rows[i]["物料编号"].ToString());
                                }
                                i8 = i8 + 1;
                            }
                            break;
                        case "12周":
                            if (i12 < 12)
                            {
                                if (dtNew.Rows[i][j].ToString().Length <= 0)
                                {
                                    i12_1 = i12_1 + 1;
                                }
                                if (i12_1 == 12)
                                {
                                    list.Add(dtNew.Rows[i]["物料编号"].ToString());
                                }
                                i12 = i12 + 1;
                            }
                            break;
                    }
                    #endregion

                }
                //初始化i值
                i2 = 0;
                i2_1 = 0;
                i4 = 0;
                i4_1 = 0;
                i8 = 0;
                i8_1 = 0;
                i12 = 0;
                i12_1 = 0;

            }
            //得到符合条件的物料编号
            if (Weeks == "库存")
            {
                sbFilter.AppendFormat(@" L.MATERIAL_NO IN {0}", MaterialNo);
            }
            else
            {
                sbFilter.AppendFormat(@" L.MATERIAL_NO IN ('{0}')", string.Join("','", list.ToArray()));
            }
                string sql1 = @" select  L.MATERIAL_NO,M.Description,SUM(QUANTITY) QTY FROM [StorageManager].[dbo].[T3_LAYER] L join
 [StorageManager].[dbo].[T3_Material] M ON L.MATERIAL_NO=M.Material_IDNo
where " + sbFilter + " group by L.MATERIAL_NO,M.Description";
            DataTable dtResult = BaseBuildSql.GetDataTableBySql(sql1);
            return dtResult;
        }
        //导出
        public override void DoExport(IButtonInfo sender)
        {
            try
            {
                SaveFileDialog dlg = new SaveFileDialog();
                //dlg.Filter = "PDF file(*.pdf)|*.pdf";
                dlg.Filter = "Excel文件(*.xls)|*.xls";
                DialogResult dialogResult = dlg.ShowDialog(this);
                if (dialogResult == DialogResult.OK)
                {
                    gridView1.ExportToXls(dlg.FileName);
                    DevExpress.XtraEditors.XtraMessageBox.Show("导出成功", "提示", MessageBoxButtons.OK);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        /// <summary>
        /// 行转列
        /// </summary>
        /// <param name="dataTable">需要进行行转列的表</param>
        /// <param name="groupingColumn">需要进行行转列的字段</param>
        /// <param name="nameColumn">名称列</param>
        /// <param name="dataColumn">数据列</param>
        /// <returns></returns>
        private DataTable createTable(DataTable dataTable, string groupingColumn, string nameColumn, string dataColumn)
        {
            //取出不重复的需要转换的列的数据
            DataTable distinct_GroupingColumn = dataTable.DefaultView.ToTable(true, groupingColumn);
            //取出不重复的名称列
            DataTable dictinct_NameColumn = dataTable.DefaultView.ToTable(true, nameColumn);
            //构建新表
            DataTable table = new DataTable();
            #region 构建新表的列
            //将名称列添加进新表
            DataColumn newColumn = new DataColumn();
            newColumn.ColumnName = nameColumn;
            //添加列   所以要.Columns
            table.Columns.Add(newColumn);
            //添加需要转换的列            
            foreach (DataRow dr in distinct_GroupingColumn.Rows)
            {
                newColumn = new DataColumn();
                newColumn.ColumnName = dr[groupingColumn].ToString();
                table.Columns.Add(newColumn);
            }
            #endregion
            #region 向新表中添加数据
            DataRow newRow;
            DataRow[] dnewRow;
            foreach (DataRow item in dictinct_NameColumn.Rows)
            {
                //添加一个新行
                newRow = table.NewRow();
                //为此新行添加第一个行数据
                newRow[nameColumn] = item[nameColumn].ToString();
                //为此新行添加列数据
                foreach (DataRow r in distinct_GroupingColumn.Rows)
                {
                    dnewRow = dataTable.Select(nameColumn + " ='" + item[nameColumn].ToString() + "' and " + groupingColumn + " ='" + r[groupingColumn].ToString() + "'");
                    if (dnewRow.Length != 0)
                    {
                        //将数据与新建表进行连合
                        newRow[r[groupingColumn].ToString()] = dnewRow[0][dataColumn];
                    }
                }
                table.Rows.Add(newRow);
            }
            #endregion

            return table;
        }
    
    }
}
